USE [REZP8WB01]
GO
/****** Object:  StoredProcedure [dbo].[Select_PaxByFltnoFltdate]    Script Date: 12/07/2010 16:31:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Select_PaxByFltnoFltdate]
(
	@pFltno    varchar(4),
	@pFltdatefrom    date,
	@pFltdateto    date
) 
AS 
    -- Action: Get flight manifiest by certain flight number and duration
    -- Users: Call center Manager and Supervisor
    -- Usages: Get flight manifiest for moving flights or cancelling flights
    -- Written by: Nguyen Hong Viet (84)983523935 vietnh@airmekong.vn
    -- Created/Modified date: 07-DEC-2010/07-DEC-2010
BEGIN
	SET NOCOUNT ON;
    SELECT 
      pjs.FlightNumber,@pFltdatefrom,
      bo.RecordLocator,
      flstat = case
          when bo.status=0 then 'Default'
          when bo.status=1 then 'Hold'
          when bo.status=2 then 'Confirmed'
          when bo.status=3 then 'Closed'
          when bo.status=4 then 'HoldCancelled'
          when bo.status=5 then 'PendingArchive'
          else                  'Archive'
        end,
      bp.PaxType,bp.LastName + '/' + bp.FirstName as PaxName,
      convert(date,pjs.DepartureDate,103) deptdate,pjs.DepartureStation,pjs.ArrivalStation,
      bc.LastName+'/'+bc.FirstName+' Tel: '+bc.HomePhone as ContactInfo
    FROM REZP8OD01.dbo.booking bo
      inner join REZP8OD01.dbo.BookingContact bc on bo.BookingID=bc.BookingID
      inner join REZP8OD01.dbo.BookingPassenger bp on bo.BookingID=bp.BookingID
      inner join REZP8OD01.dbo.PassengerJourneySegment pjs on bp.PassengerID=pjs.PassengerID
    WHERE
      pjs.FlightNumber = right(replicate(' ',4)+@pFltno,4) 
      and convert(date,pjs.DepartureDate,103)
          between convert(date,@pFltdatefrom,103) and convert(date,@pFltdateto,103);
END